﻿using System;
using System.Collections.Generic;
using System.Text;
using combit.ListLabel16.DataProviders;
using System.Data.SQLite;
using System.Data;
using System.Text.RegularExpressions;
using System.Runtime.Serialization;
using System.Security.Permissions;
using System.Globalization;


namespace combit.ListLabel16.DataProviders
{
    /// <summary>
    /// Provider for SQLite connection, http://sqlite.phxsoftware.com/
    /// Tested with version 1.0.61.0
    /// </summary>
    [Serializable]
    public sealed class SQLiteConnectionDataProvider : DbConnectionDataProvider, ISerializable
    {       
        public SQLiteConnectionDataProvider(SQLiteConnection connection)
        {
            Connection = connection as IDbConnection;
        }

        private SQLiteConnectionDataProvider()
            : base()
        {
        }

        private SQLiteConnectionDataProvider(SerializationInfo info, StreamingContext context)
            : base(info, context)
        {
            if (info.GetInt32("SQLiteConnectionDataProvider.Version") >= 1)
            {
                Connection = new SQLiteConnection();
                Connection.ConnectionString = info.GetString("ConnectionString");
            }
        }


        protected override void Init()
        {           
            if (Initialized)
                return;

            List<String> passedRelationNames = new List<string>();
            
            Connection.Open();
            SQLiteCommand cmd;
            try
            {
                DataTable dt = (Connection as SQLiteConnection).GetSchema("Tables");
                Connection.Close();
                
                foreach (DataRow dr in dt.Rows)
                {
                    string tableName = dr["TABLE_NAME"].ToString();
                    cmd = new SQLiteCommand(String.Format(CultureInfo.InvariantCulture, "Select * from {0}", tableName), ((Connection as ICloneable).Clone()) as SQLiteConnection);
                    Provider.AddCommand(cmd as IDbCommand, tableName, "\"{0}\"", "@{0}");
                }

                string commandText = "SELECT sql FROM sqlite_master WHERE type='table'";
                Connection.Open();
                cmd = new  SQLiteCommand(commandText, Connection as SQLiteConnection);
                SQLiteDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                
                while (reader.Read())
                {
                    if (reader.GetString(0).ToUpper(CultureInfo.InvariantCulture).Contains("REFERENCES"))
                    {
                        Regex r = new Regex("(.*?) .*? CONSTRAINT [^ ]*? REFERENCES (.*?)\\((?: )*(.*?)(?: )*\\)", RegexOptions.IgnoreCase);
                        Regex p = new Regex("CREATE TABLE ([^ ]*?)[ (]*?\\(");

                        MatchCollection mc = r.Matches(reader.GetString(0));
                        Match m = p.Match(reader.GetString(0));

                        foreach (Match current in mc)
                        {
                            string parentTableName = m.Groups[1].ToString();
                            string parentColumnName = current.Groups[1].ToString();
                            string childTableName = current.Groups[2].ToString();
                            string childColumnName = current.Groups[3].ToString();
                            string relName = parentTableName + "2" + childTableName;
                            int relationIndex = 1;
                            string formatString = relName + "{0}";

                            while (passedRelationNames.Contains(relName))
                            {
                                relName = String.Format(CultureInfo.InvariantCulture, formatString, relationIndex);
                                relationIndex++;
                            }
                            passedRelationNames.Add(relName);
                            Provider.AddRelation(relName, parentTableName, childTableName, parentColumnName, childColumnName);
                        }
                    }
                }
                reader.Close();
            }
            finally
            {
                Connection.Close();
                Initialized = true;
            }
        }

        #region ISerializable Members
        [SecurityPermissionAttribute(SecurityAction.Demand, SerializationFormatter = true)]
        public override void GetObjectData(SerializationInfo info, StreamingContext context)
        {
            base.GetObjectData(info, context);
            info.AddValue("SQLiteConnectionDataProvider.Version", 1);
            info.AddValue("ConnectionString", Connection.ConnectionString);
        }

        #endregion
    }
}